from openpyxl import load_workbook
import os
import timeit
import flet as fl
s1='Graph Builder(\nSize( 800,600 ),\nShow Control Panel( 0 ),\nVariables( X( :TOOL ), Y( :'
s2='), Color( :TOOL ) ),\nElements(\nPoints( X, Y, Legend( 7 ), Jitter( "Random Normal" ) ),\nBox Plot( X, Y, Legend( 8 ), Jitter(  "Random Normal" ) )\n),\nSendToReport(\nDispatch(\n{},\n"'
s3='",\nScaleBox,\n{Format( "Fixed Dec", 15, 2 ),Min('
s4='), Max( '
s5='), Inc( 0.01 ), Minor Ticks( 0 ),\nAdd Ref Line('
s6=', "Dashed", "Black", "Nominal '
s7='", 1 ),\n Add Ref Line('
s8=', "Dashed", "Red", "USL '
s9=', "Dashed", "Red", "LSL '
s10='", 1 )}\n),\nDispatch( {}, "graph title", TextEditBox, {Set Text( "SPC-'
s11='" )} )\n),\nsave picture("SPC-'
s12='.png","png")\n);\n\n\n\n\n'
s13='Data Table( "data" ) << Stack(\ncolumns('
s14=':Name("'
s15='"),'
s16='),\nSource Label Column( "'
s17='"),\nStacked Data Column( "'
s18=' Data" ),\nOutput Table( "'
s19='")\n);\nGraph Builder(\nSize( 800,600 ),\nShow Control Panel( 0 ),\nVariables(\nX( :TOOL ),\nX( :'
s20=', Position( 1 ) ),\nY( :'
s21=' Data),Color( :'
s22=')),\nElements(\nPoints( X( 1 ), X( 2 ), Y, Legend( 18 ), Jitter( "Random Normal" ) ),\nBox Plot( X( 1 ), X( 2 ), Y, Legend( 19 ), Jitter( "Random Normal" ) )\n),\nSendToReport(\nDispatch(\n{},\n"'
s23=' Data", \nScale Box, \n{ Format( \"Fixed Dec\", 15, 2 ), Min('
file_path = "D:/CPK.xlsx"
def main(page: fl.Page):
    """
    主函数，用于生成JSL脚本。
    
    Args:
        page (fl.Page): Flexx页面对象。
    
    Returns:
        None
    
    """
    page.title = "Create JSL"
    page.vertical_alignment = fl.MainAxisAlignment.CENTER
    page.horizontal_alignment = fl.CrossAxisAlignment.CENTER
    page.window.width = 300
    page.window.height = 200
    def get_jsl(e):
        """
        根据Excel文件中的数据生成JSL脚本文件。
        
        Args:
            e: 事件对象，用于触发函数。
        
        Returns:
            None
        
        """
        start_time = timeit.default_timer()
        if os.path.exists(file_path):
            wb = load_workbook(filename=file_path)
            ws = wb.active
            for row in range(2, ws.max_row + 1):
                if "-" in ws.cell(row,1).value:
                    ws.cell(row,5).value="M"
                    ws.cell(row,7).value=ws.cell(row,1).value.split("-")[0]
                else:
                    ws.cell(row,5).value="S"
            for row in range(2, ws.max_row + 1):
                cell_value = ws.cell(row, 4).value
                if not isinstance(cell_value, (float, int)):
                    ws.cell(row, 6).value = "GD&T"
                else:
                    ws.cell(row, 6).value = "Tolerance"
            unique_values = set()
            for row in range(2, ws.max_row + 1):
                cell_value = ws.cell(row, 7).value
                if isinstance(cell_value, str):
                    unique_values.add(cell_value)
            unique_list = list(unique_values)
            wb.save(file_path)
            final_result='Open("$data/JMP.xlsx");\n'
            for row in range(2, ws.max_row + 1):
                if ws.cell(row, 5).value == "S":
                    max=str(ws.cell(row, 3).value+0.02)
                    name=str(ws.cell(row, 1).value)
                    normal=str(ws.cell(row, 2).value)
                    usl=str(round(ws.cell(row, 3).value,3))
                    if ws.cell(row, 6).value != "GD&T":
                        lsl=str(round(ws.cell(row, 4).value,3))
                        min=str(ws.cell(row, 4).value-0.02)
                        S=s1+name+s2+name+s3+min+s4+max+s5+normal+s6+normal+s7+usl+s8+usl+s7+lsl+s9+lsl+s10+name+s11+name+s12
                        final_result=final_result+S
                    else:
                        min='-0.005'
                        S=s1+name+s2+name+s3+min+s4+max+s5+normal+s6+normal+s7+usl+s8+usl+s10+name+s11+name+s12
                        final_result=final_result+S
            for multi in unique_list:
                ss=''
                row=2
                name=multi
                for rows in range(2, ws.max_row + 1):
                    if ws.cell(rows, 7).value==multi:
                        ss=ss+s14+str(ws.cell(rows, 1).value)+s15
                        row=rows
                normal=str(ws.cell(row, 2).value)
                usl=str(round(ws.cell(row, 3).value,3))
                max=str(ws.cell(row, 3).value+0.02)
                if ws.cell(row, 6).value != "GD&T":
                    lsl=str(round(ws.cell(row, 4).value,3))
                    min=str(ws.cell(row, 4).value-0.02)
                    S=s13+ss+s16+name+s17+name+s18+name+s19+name+s20+name+s21+name+s22+name+s23+min+s4+max+s5+normal+s6+normal+s7+usl+s8+usl+s7+lsl+s9+lsl+s10+name+s11+name+s12
                    final_result=final_result+S
                else:
                    min='-0.005'
                    S=s13+ss+s16+name+s17+name+s18+name+s19+name+s20+name+s21+name+s22+name+s23+min+s4+max+s5+normal+s6+normal+s7+usl+s8+usl+s10+name+s11+name+s12
                    final_result=final_result+S
            f = open("d:/CPK.jsl", "w")
            f.write(final_result)
            f.close()
            end_time = timeit.default_timer()
            t="脚本已生成D盘CPK.jsl，耗时"+str(round(end_time - start_time,5))+"秒"
            lb.value=t
            page.update()
        else:
            lb.value="文件未找到，请确认"
            page.update()
    btn=fl.ElevatedButton(text="生成脚本", on_click=get_jsl,width=280,height=120,bgcolor=fl.colors.DEEP_ORANGE_900,color=fl.colors.LIGHT_BLUE_300,style=fl.ButtonStyle(shape=fl.RoundedRectangleBorder(radius=5)))
    lb=fl.Text(value="",color=fl.colors.GREEN_400)
    page.add(btn,lb)
fl.app(target=main)